import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Random;

import common.NoRepeatRandom;
import common.SQLCall;

public class PopulateStudents
{
	Connection conn;
	ArrayList<Integer> users;
	Random rnd = new Random();

	public static void main(String[] args)
	{
		PopulateStudents pop = new PopulateStudents();
		try
		{
			pop.connect();
		} catch (SQLException sqlError)
		{
			sqlError.printStackTrace();
		}

		// pop.setup();
		pop.generate();

		try
		{
			pop.close();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

	public PopulateStudents()
	{

	}

	public PopulateStudents(Connection c)
	{
		conn = c;
	}

	public void connect() throws SQLException
	{
		DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
		conn = DriverManager.getConnection(
				"jdbc:oracle:thin:@146.187.134.17:1542:oracle10g", "cscd494",
				"R3tral435");

	}

	public void setup()
	{
		users = SQLCall.getStudentUsers(conn);
	}

	public void close() throws SQLException
	{
		conn.close();
	}

	public void generate()
	{
		ArrayList<Integer> userList = new ArrayList<Integer>();
		ArrayList<Integer> examList = new ArrayList<Integer>();
		try
		{
			PreparedStatement pst = conn.prepareStatement("SELECT student_id "
					+ "FROM STUDENT_USER");
			ResultSet user = pst.executeQuery();

			while (user.next())
			{
				userList.add(user.getInt("student_id"));
			}

			user.close();

			pst = conn.prepareStatement("SELECT exam_id FROM EXAM");

			ResultSet exam = pst.executeQuery();
			while (exam.next())
			{
				examList.add(exam.getInt("exam_id"));
			}
			exam.close();
			for (int i = 0; i < examList.size(); i++)
			{
				int examID = examList.get(i);
				ArrayList<Integer> studentList = new ArrayList<Integer>();
				NoRepeatRandom randGen = new NoRepeatRandom();
				int count = 0;
				try
				{
					while (count < 10)
					{
						studentList.add(userList.get(randGen
								.getNextInt(userList.size())));
						count++;
					}
				} catch (Exception e)
				{
					// e.printStackTrace();
				}
				pst = conn.prepareStatement("SELECT question_id, q_version "
						+ "FROM EXAM_QUESTION where exam_id=?");
				pst.setInt(1, examID);
				ResultSet examQ = pst.executeQuery();

				PreparedStatement upload = conn
						.prepareStatement("INSERT INTO STUDENT_QUESTION (student_question_id, student_id, question_id, "
								+ "q_version, exam_id, grade, notes) "
								+ "VALUES(student_question_source_s1.nextval, ?, ? ,0, ?, ?, 'notes')");
				while (examQ.next())
				{
					// examQ.next();
					int qID = examQ.getInt("question_id");
					int qVer = examQ.getInt("q_version");
					System.out.println(qID);
					for (int j = 0; j < studentList.size(); j++)
					{
						// System.out.println(studentList.get(j));
						upload.setInt(1, studentList.get(j));
						upload.setInt(2, qID);
						upload.setInt(3, examID);
						upload.setInt(4, rnd.nextInt(SQLCall.getQuestionPoints(
								conn, qID, qVer)));

						upload.execute();
					}
				}
				examQ.close();
				upload.close();
			}

			pst.close();
		} catch (SQLException sqlError)
		{
			sqlError.printStackTrace();
		}
	}
}
